package cn.vastbase.jdbc.bigdata;

import org.junit.Test;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;

public class ByteTestCase {

    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "cn.com.vastbase.Driver";
    static String url = "jdbc:vastbase://192.168.56.12:5000/test?loggerLevel=OFF";
    static String username = "test";
    static String passwd = "Vastdata#0730";
    static CallableStatement cs = null;
    static Statement stmt = null;
    static PreparedStatement ps = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;

    static Reader reader = null;
    static Writer writer = null;

    static InputStream is = null;
    static OutputStream os = null;


    /**
     * CREATE TABLE test_bytea (id integer, img bytea);
     * 使用setBinaryStream，getBinaryStream存取
     */
    @Test
    public void testBytea() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        File file = new File("src/main/resources/inimage.png");
        try {

            ps = conn.prepareStatement("delete from test_bytea");
            ps.execute();

            InputStream is = Files.newInputStream(file.toPath());
            byte[] bytes = new byte[(int) file.length()];
            is.read(bytes);
            ps = conn.prepareStatement("INSERT INTO test_bytea VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBytes(2, bytes);
            ps.executeUpdate();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_bytea WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                byte[] imgBytes = rs.getBytes(1);
                os = Files.newOutputStream(Paths.get("src/main/resources/outimage.png"));
                os.write(imgBytes);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * CREATE TABLE test_blob (id integer, img blob);
     * 使用setBinaryStream，getBinaryStream存取
     */
    //TODO oraBlobMode 参数
    @Test
    public void testBlob() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        File file = new File("src/main/resources/inimage.png");
        try {
            ps = conn.prepareStatement("delete from test_blob");
            ps.execute();

            InputStream is = Files.newInputStream(file.toPath());
            ps = conn.prepareStatement("INSERT INTO test_blob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBinaryStream(2, is);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_blob WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                is = rs.getBinaryStream(1);
                byte[] data = new byte[is.available()];
                is.read(data);
                os = Files.newOutputStream(Paths.get("src/main/resources/outimage.png"));
                os.write(data);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * 使用setBlob，getBlob存取
     */
    @Test
    public void testBlob2() {
        cname = "cn.com.vastbase.Driver";
        url = "jdbc:vastbase://192.168.56.17:6000/test?loggerLevel=OFF";
        username = "test";
        passwd = "Vastdata#0730";

        if (conn() == null) {
            return;
        }
        try {
            ps = conn.prepareStatement("delete from test_blob");
            ps.execute();

            String xmlData = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                    "<library>\n" +
                    "    <book id=\"bk101\">\n" +
                    "        <author>Gambardella, Matthew</author>\n" +
                    "        <title>XML Developer's Guide</title>\n" +
                    "        <genre>Computer</genre>\n" +
                    "        <price>44.95</price>\n" +
                    "        <publish_date>2000-10-01</publish_date>\n" +
                    "        <description>An in-depth look at creating applications with XML.</description>\n" +
                    "    </book>\n" +
                    "    <!-- 其他书籍信息 -->\n" +
                    "</library>";

            byte[] xmlBytes = xmlData.getBytes(StandardCharsets.UTF_8);
            is = new ByteArrayInputStream(xmlBytes);

            ps = conn.prepareStatement("INSERT INTO test_blob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBlob(2, is);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_blob WHERE id= ? limit 1");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getBlob(1).toString());
//                is = rs.getBlob(1).getBinaryStream();
//                byte[] data = new byte[is.available()];
//                is.read(data);

//                File file = new File("src/main/resources/outimage.png");
//                file.delete();
//                os = Files.newOutputStream(file.toPath());
//                os.write(data);
//                os.flush();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void testBlob3() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        try {
            ps = conn.prepareStatement("delete from test_blob");
            ps.execute();

            InputStream is = Files.newInputStream(new File("src/main/resources/inimage.png").toPath());
            ps = conn.prepareStatement("INSERT INTO test_blob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBlob(2, is);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_blob WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                is = rs.getBlob(1).getBinaryStream();
                byte[] data = new byte[is.available()];
                is.read(data);

                File file = new File("src/main/resources/outimage.png");
                file.delete();
                os = Files.newOutputStream(file.toPath());
                os.write(data);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    @Test
    public void testImage() {
        url = "jdbc:vastbase://192.168.56.12:8000/vastbase?loggerLevel=OFF";
        username = "test";
        passwd = "Vastdata#0730";
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        try {
            ps = conn.prepareStatement("drop table if exists test_image; create table if not exists test_image(id int,img image);");
            ps.execute();

            InputStream is = Files.newInputStream(new File("src/main/resources/inimage.png").toPath());
            byte[] bytes = new byte[is.available()];
            is.read(bytes);
//            System.out.println(Arrays.toString(bytes));
            ps = conn.prepareStatement("INSERT INTO test_image VALUES (?, ?)");
            ps.setInt(1, 1);
            Blob blob = conn.createBlob();
            blob.setBytes(1, bytes);
            ps.setBlob(2, blob);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
//            ps = conn.prepareStatement("SELECT image_col FROM tb_1 WHERE id= ?");
//            ps.setInt(1, 1);


           /* ResultSet rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            String columnTypeName = metaData.getColumnTypeName(1);
            System.out.println(columnTypeName);

            while (rs.next()) {
                Object object = rs.getObject(1);
                System.out.println(object.getClass().getName());
                System.out.println(object);
            }*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("[FAIL] conn database fail." + e.getMessage());
        }
        return null;
    }

    public void close() {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            if (cs != null)
                cs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close callableStatement fail." + e.getMessage());
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close preparedStatement fail." + e.getMessage());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
        try {
            if (writer != null)
                writer.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close writer fail." + e.getMessage());
        }
        try {
            if (reader != null)
                reader.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close reader fail." + e.getMessage());
        }
        try {
            if (os != null)
                os.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close outputStream fail." + e.getMessage());
        }
        try {
            if (is != null)
                is.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close inputStream fail." + e.getMessage());
        }

    }

}
